package com.boesch.electroOffice.dataaccess.daoImpl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

import com.boesch.electroOffice.dataaccess.common.IllegalDataAccessException;
import com.boesch.electroOffice.dataaccess.common.ReturnCodesDataaccess;
import com.boesch.electroOffice.dataaccess.dao.IdentifierDao;
import com.boesch.electroOffice.dataaccess.dao.WorkingHourDao;
import com.boesch.electroOffice.dataaccess.mapper.WorkingHourMapper;
import com.boesch.electroOffice.model.electroOffice.Identifier;
import com.boesch.electroOffice.model.electroOffice.WorkingHour;
import com.boesch.electroOffice.model.wrapper.WeekReportEntry;
import com.boesch.electroOffice.model.wrapper.WorkingHoursEntry;
import com.boesch.electroOffice.utils.DateUtils;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.model.User;

/**
 * This class executes the sql statements on the database for class
 * WorkingHourElectroOffice. If a Exception is thrown, it will be unpacked to a
 * illegalDataAccessException including a return code which can be shown to the
 * user.
 * 
 * @author Ralph Koster
 * 
 */
@Repository
public class WorkingHourDaoImpl implements WorkingHourDao, Serializable {

	private static final long serialVersionUID = 5213657302972326288L;
	private static final Log log = LogFactoryUtil.getLog(WorkingHourDaoImpl.class);

	@Autowired
	private IdentifierDao identifierDao;
	@Autowired
	private JdbcTemplate eoJdbcTemplate;
	@Autowired
	private TransactionTemplate eoTransactionTemplate;

	@Value("${electroOffice.queryReadWorkingHoursByAppointmentId}")
	private String queryReadWorkingHoursByAppointmentId;
	@Value("${electroOffice.queryReadWorkingHoursByAppointmentIdStartDateAndEndDate}")
	private String queryReadWorkingHoursByAppointmentIdStartDateAndEndDate;
	@Value("${electroOffice.queryReadWorkingHoursByUserIdStartDateEndDateAndIdentifierName}")
	private String queryReadWorkingHoursByUserIdStartDateEndDateAndIdentifierName;
	@Value("${electroOffice.queryReadWorkingHoursByUserIdStartDateEndDateAndIdentifierKind}")
	private String queryReadWorkingHoursByUserIdStartDateEndDateAndIdentifierKind;
	@Value("${electroOffice.queryCreateWorkingHour}")
	private String queryCreateWorkingHour;
	@Value("${electroOffice.queryUpdateWorkingHour}")
	private String queryUpdateWorkingHour;
	@Value("${electroOffice.queryDeleteWorkingHour}")
	private String queryDeleteWorkingHour;

	/**
	 * {@inheritDoc}
	 */
	public List<WorkingHour> readWorkingHoursByAppointmentId(int appointmentId) throws IllegalDataAccessException {
		List<WorkingHour> workingHours = new ArrayList<WorkingHour>();
		try {
			workingHours = eoJdbcTemplate.query(queryReadWorkingHoursByAppointmentId, new Object[] { appointmentId }, new WorkingHourMapper(identifierDao));
			return workingHours;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public List<WorkingHour> readWorkingHoursByAppointmentIdStartDateAndEndDate(long appointmentId, Timestamp startDate, Timestamp endDate)
			throws IllegalDataAccessException {
		List<WorkingHour> workingHours = new ArrayList<WorkingHour>();
		try {
			workingHours = eoJdbcTemplate.query(queryReadWorkingHoursByAppointmentIdStartDateAndEndDate, new Object[] { appointmentId, startDate, endDate },
					new WorkingHourMapper(identifierDao));
			return workingHours;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public List<WorkingHour> readWorkingHoursByUserIdStartDateEndDateAndIdentifier(long userId, Timestamp startDate, Timestamp endDate, Identifier identifier)
			throws IllegalDataAccessException {
		List<WorkingHour> workingHours = new ArrayList<WorkingHour>();
		try {
			workingHours = eoJdbcTemplate.query(queryReadWorkingHoursByUserIdStartDateEndDateAndIdentifierName,
					new Object[] { userId, startDate, endDate, identifier.getName() }, new WorkingHourMapper(identifierDao));
			return workingHours;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public List<WorkingHour> readWorkingHoursByUserIdStartDateEndDateAndIdentifierKind(long userId, Timestamp startDate, Timestamp endDate, String identifierKind)
			throws IllegalDataAccessException {
		List<WorkingHour> workingHours = new ArrayList<WorkingHour>();
		try {
			workingHours = eoJdbcTemplate.query(queryReadWorkingHoursByUserIdStartDateEndDateAndIdentifierKind,
					new Object[] { userId, startDate, endDate, identifierKind }, new WorkingHourMapper(identifierDao));
			return workingHours;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String saveWorkingHour(WorkingHour workingHour, User currentUser) throws IllegalDataAccessException {
		try {
			return insertWorkingHour(workingHour, currentUser);
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.CREATION_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String updateWorkingHour(WorkingHour workingHour, User currentUser) throws IllegalDataAccessException {
		try {
			return updateWorkinghour(workingHour, currentUser);
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.UPDATE_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String deleteWorkingHour(final WorkingHour workingHour) throws IllegalDataAccessException {
		int result = eoJdbcTemplate.update(queryDeleteWorkingHour, workingHour.getId());
		if (result == 1) {
			return ReturnCodesDataaccess.DELETED.getValue();
		} else {
			return ReturnCodesDataaccess.DELETION_ERROR.getValue();
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String deleteWorkingHoursEntry(final WorkingHoursEntry entry, final User currentUser) throws IllegalDataAccessException {
		try {
			eoTransactionTemplate.execute(new TransactionCallbackWithoutResult() {
				@Override
				protected void doInTransactionWithoutResult(TransactionStatus status) {
					if (null != entry.getMonday() && entry.getMonday().getVersion() > 0) {
						try {
							deleteWorkingHour(entry.getMonday());
						} catch (IllegalDataAccessException e) {
							log.error("Failed to delete workingHour!", e);
						}
					}
					if (null != entry.getTuesday() && entry.getTuesday().getVersion() > 0) {
						try {
							deleteWorkingHour(entry.getTuesday());
						} catch (IllegalDataAccessException e) {
							log.error("Failed to delete workingHour!", e);
						}
					}
					if (null != entry.getWednesday() && entry.getWednesday().getVersion() > 0) {
						try {
							deleteWorkingHour(entry.getWednesday());
						} catch (IllegalDataAccessException e) {
							log.error("Failed to delete workingHour!", e);
						}
					}
					if (null != entry.getThursday() && entry.getThursday().getVersion() > 0) {
						try {
							deleteWorkingHour(entry.getThursday());
						} catch (IllegalDataAccessException e) {
							log.error("Failed to delete workingHour!", e);
						}
					}
					if (null != entry.getFriday() && entry.getFriday().getVersion() > 0) {
						try {
							deleteWorkingHour(entry.getFriday());
						} catch (IllegalDataAccessException e) {
							log.error("Failed to delete workingHour!", e);
						}
					}
					if (null != entry.getSaturday() && entry.getSaturday().getVersion() > 0) {
						try {
							deleteWorkingHour(entry.getSaturday());
						} catch (IllegalDataAccessException e) {
							log.error("Failed to delete workingHour!", e);
						}
					}
					if (null != entry.getSunday() && entry.getSunday().getVersion() > 0) {
						try {
							deleteWorkingHour(entry.getSunday());
						} catch (IllegalDataAccessException e) {
							log.error("Failed to delete workingHour!", e);
						}
					}
				}
			});
			log.debug("Inserted all workingHours for weekReportEntry");
			return ReturnCodesDataaccess.DELETED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.DELETION_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String saveWorkingHoursEntry(final WorkingHoursEntry entry, final User currentUser) throws IllegalDataAccessException {
		try {
			eoTransactionTemplate.execute(new TransactionCallbackWithoutResult() {
				@Override
				protected void doInTransactionWithoutResult(TransactionStatus status) {
					if (null != entry.getMonday() && entry.getMonday().getHours() > 0) {
						if (entry.getMonday().getId() > 0) {
							updateWorkinghour(entry.getMonday(), currentUser);
						} else {
							insertWorkingHour(entry.getMonday(), currentUser);
						}
					}
					if (null != entry.getTuesday() && entry.getTuesday().getHours() > 0) {
						if (entry.getTuesday().getId() > 0) {
							updateWorkinghour(entry.getTuesday(), currentUser);
						} else {
							insertWorkingHour(entry.getTuesday(), currentUser);
						}
					}
					if (null != entry.getWednesday() && entry.getWednesday().getHours() > 0) {
						if (entry.getWednesday().getId() > 0) {
							updateWorkinghour(entry.getWednesday(), currentUser);
						} else {
							insertWorkingHour(entry.getWednesday(), currentUser);
						}
					}
					if (null != entry.getThursday() && entry.getThursday().getHours() > 0) {
						if (entry.getThursday().getId() > 0) {
							updateWorkinghour(entry.getThursday(), currentUser);
						} else {
							insertWorkingHour(entry.getThursday(), currentUser);
						}
					}
					if (null != entry.getFriday() && entry.getFriday().getHours() > 0) {
						if (entry.getFriday().getId() > 0) {
							updateWorkinghour(entry.getFriday(), currentUser);
						} else {
							insertWorkingHour(entry.getFriday(), currentUser);
						}
					}
					if (null != entry.getSaturday() && entry.getSaturday().getHours() > 0) {
						if (entry.getSaturday().getId() > 0) {
							updateWorkinghour(entry.getSaturday(), currentUser);
						} else {
							insertWorkingHour(entry.getSaturday(), currentUser);
						}
					}
					if (null != entry.getSunday() && entry.getSunday().getHours() > 0) {
						if (entry.getSunday().getId() > 0) {
							updateWorkinghour(entry.getSunday(), currentUser);
						} else {
							insertWorkingHour(entry.getSunday(), currentUser);
						}
					}
				}
			});
			log.debug("Inserted all workingHours for workingHoursEntry");
			return ReturnCodesDataaccess.CREATED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.CREATION_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String saveWeekReportEntry(final WeekReportEntry entry, final User currentUser) throws IllegalDataAccessException {
		return saveWorkingHoursEntry(entry, currentUser);
	}

	/**
	 * {@inheritDoc}
	 */
	private String insertWorkingHour(final WorkingHour workingHour, final User currentUser) throws DataAccessException {
		eoJdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(queryCreateWorkingHour);
				ps.setDate(1, DateUtils.convertLocalDateToDate(workingHour.getWorkdate()));
				ps.setDouble(2, workingHour.getHours());
				ps.setObject(3, workingHour.getId());
				ps.setString(4, currentUser.getFullName());
				ps.setTimestamp(5, DateUtils.getCurrentTimestamp());
				ps.setObject(6, 1);
				ps.setObject(7, workingHour.getLrUserId());
				ps.setObject(8, workingHour.getAppointmentId());
				return ps;
			}
		});
		log.debug("Created workingHour successfully");
		return ReturnCodesDataaccess.CREATED.getValue();
	}

	/**
	 * {@inheritDoc}
	 */
	private String updateWorkinghour(final WorkingHour workingHour, final User currentUser) throws DataAccessException {
		eoJdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(queryUpdateWorkingHour);
				ps.setDouble(1, workingHour.getHours());
				ps.setString(2, currentUser.getFullName());
				ps.setInt(3, workingHour.getVersion() + 1);
				ps.setInt(4, workingHour.getId());
				return ps;
			}
		});
		log.debug("Updated workingHour successfully");
		return ReturnCodesDataaccess.UPDATED.getValue();
	}
}
